Imports Microsoft.VisualBasic



Namespace Persistencia.ModuloPlanificacion

    Public Class ControladorPersistencia

#Region "Atributos"

        Private Shared instance As ControladorPersistencia
        Private query As String
        Private BD As Persistencia.ModuloPlanificacion.bd

#End Region

        Public Sub New()
            BD = Persistencia.ModuloPlanificacion.bd.getInstance
        End Sub

#Region "getInstance"
        'Patron Singleton 
        Public Shared Function getInstance() As ControladorPersistencia
            If instance Is Nothing Then
                instance = New ControladorPersistencia
            End If
            Return instance
        End Function
#End Region

#Region "Obtener"

        Public Function ObtenerOT(ByVal criterio As String, ByVal op As Integer) As ArrayList

            Dim query As String = ""

            If op = 1 Then
                query = "select * from ordentrabajo"
            End If

            If op = 2 Then
                query = "select * from ordentrabajo where id=" & criterio
            End If

            Return BD.ObtenerOt(query)
        End Function

        Public Function ObtenerGrupo(ByVal criterio As String) As ArrayList
            Dim query As String = "select * from grupousuarios where id=" & criterio
            Return BD.ObtenerGrupo(query)
        End Function

        Public Function ObtenerDato(ByVal query As String) As Hashtable
            Return BD.EjecutarConRetorno(query)
        End Function

        Public Function ObtenerCriterioConsulta() As ArrayList
            Dim query As String = "select * from criterioconsultaplanificacion"
            Return BD.ObtenerCriterioConsulta(query)
        End Function

        Public Function ObtenerPlanificacionPrincipal(ByVal criterio As String, ByVal op As Integer) As ArrayList
            Dim query As String = ""

            If op = 1 Then
                query = "select * from plan_planificacion"
            End If

            If op = 2 Then
                query = "select * from plan_planificacion where id=" & criterio
            End If

            If op = 3 Then
                query = "select * from plan_planificacion where ot_fk=" & criterio
            End If

            Return BD.ObtenerPlanificacion(query)
        End Function

        Public Function ObtenerPlanificacionM1(ByVal criterio As String, ByVal op As Integer) As ArrayList
            Dim query As String = ""

            If op = 1 Then
                query = "select * from plan_planificacion_m1"
            End If

            If op = 2 Then
                query = "select * from plan_planificacion_m1 where id=" & criterio
            End If

            If op = 3 Then
                query = "select * from plan_planificacion_m1 where fk_planificacion=" & criterio
            End If
            Try
                Return BD.ObtenerPlanificacionM1(query)
            Catch ex As Exception
                Throw ex
            End Try

        End Function

        Public Function ObtenerPlanificacionM2(ByVal criterio As String, ByVal op As Integer) As ArrayList

            Dim query As String = ""

            If op = 1 Then
                query = "select * from plan_planificacion_m2"
            End If

            If op = 2 Then
                query = "select * from plan_planificacion_m2 where id=" & criterio
            End If

            If op = 3 Then
                query = "select * from plan_planificacion_m2 where pk_plani_m1=" & criterio
            End If
            Return BD.ObtenerPlanificacionM2(query)
        End Function

        Public Function ObtenerPlanificacionM3(ByVal criterio As String, ByVal op As Integer) As ArrayList
            Dim query As String = ""
            'obtiene todas mas m3
            If op = 1 Then
                query = "SELECT " & _
                    "plan_planificacion_m3.id, " & _
                    "plan_planificacion_m3.estacion, " & _
                    "plan_planificacion_m3.tiempo, " & _
                    "plan_planificacion_m3.observacion, " & _
                    "plan_planificacion_m3.fk_plani_m2, " & _
                    "CAST(plan_planificacion_m1.cantidad as int) * plan_planificacion_m2.cantidad as total " & _
                    "FROM " & _
                    "public.plan_planificacion_m3 "

            End If
            'obtiene m3 segun id
            If op = 2 Then
                query = "SELECT " & _
                          "plan_planificacion_m3.id, " & _
                          "plan_planificacion_m3.estacion, " & _
                          "plan_planificacion_m3.tiempo, " & _
                          "plan_planificacion_m3.observacion, " & _
                          "plan_planificacion_m3.fk_plani_m2, " & _
                          "CAST(plan_planificacion_m1.cantidad as int) * plan_planificacion_m2.cantidad as total " & _
                          "FROM " & _
                          "public.plan_planificacion_m3, " & _
                          "public.plan_planificacion_m2, " & _
                          "public.plan_planificacion_m1 " & _
                          "WHERE " & _
                          "plan_planificacion_m3.fk_plani_m2 = plan_planificacion_m2.id AND " & _
                          "plan_planificacion_m2.pk_plani_m1 = plan_planificacion_m1.id AND " & _
                          "plan_planificacion_m3.id=" & criterio

            End If
            'obtiene m3 que pertenecen a un m2
            If op = 3 Then
                query = "SELECT " & _
                    "plan_planificacion_m3.id, " & _
                    "plan_planificacion_m3.estacion, " & _
                    "plan_planificacion_m3.tiempo, " & _
                    "plan_planificacion_m3.observacion, " & _
                    "plan_planificacion_m3.fk_plani_m2, " & _
                    "CAST(plan_planificacion_m1.cantidad as int) * plan_planificacion_m2.cantidad as total " & _
                    "FROM " & _
                    "public.plan_planificacion_m3, " & _
                    "public.plan_planificacion_m2, " & _
                    "public.plan_planificacion_m1 " & _
                    "WHERE " & _
                    "plan_planificacion_m3.fk_plani_m2 = plan_planificacion_m2.id AND " & _
                    "plan_planificacion_m2.pk_plani_m1 = plan_planificacion_m1.id AND " & _
                    "fk_plani_m2=" & criterio

            End If
            'Obtiene m3 que no estan planificadas pertenecientes a una estacion
            If op = 4 Then
                query = "SELECT " & _
                        "plan_planificacion_m3.id, " & _
                        "plan_planificacion_m3.estacion, " & _
                        "plan_planificacion_m3.tiempo, " & _
                        "plan_planificacion_m3.observacion, " & _
                        "plan_planificacion_m3.fk_plani_m2, " & _
                        "(CAST(plan_planificacion_m1.cantidad as int) * plan_planificacion_m2.cantidad) as total " & _
                        "FROM " & _
                        "public.plan_planificacion_m3, " & _
                        "public.plan_planificacion_m2, " & _
                        "public.plan_planificacion_m1, " & _
                        "public.plan_programar " & _
                        "WHERE " & _
                        "plan_planificacion_m3.fk_plani_m2 = plan_planificacion_m2.id AND " & _
                        "plan_planificacion_m2.pk_plani_m1 = plan_planificacion_m1.id AND " & _
                        "plan_planificacion_m3.id not in (select p2.fk_plan_m3 from  public.plan_programar p2) AND " & _
                        "plan_planificacion_m3.estacion='" & criterio & "'" & _
                        "group by plan_planificacion_m3.id,plan_planificacion_m1.cantidad,plan_planificacion_m2.cantidad " & _
                        "order by id "

            End If
            'obtiene m3 planificadas a medias
            If op = 5 Then
                query = "SELECT " & _
                        "plan_planificacion_m3.id, " & _
                        "plan_planificacion_m3.estacion, " & _
                        "plan_planificacion_m3.tiempo, " & _
                        "plan_planificacion_m3.observacion, " & _
                        "plan_planificacion_m3.fk_plani_m2, " & _
                        "(CAST(plan_planificacion_m1.cantidad as int) * plan_planificacion_m2.cantidad) as total " & _
                        "FROM " & _
                        "public.plan_planificacion_m3, " & _
                        "public.plan_planificacion_m2, " & _
                        "public.plan_planificacion_m1, " & _
                        "public.plan_programar " & _
                        "WHERE " & _
                        "plan_planificacion_m3.fk_plani_m2 = plan_planificacion_m2.id AND " & _
                        "plan_planificacion_m2.pk_plani_m1 = plan_planificacion_m1.id AND " & _
                        "plan_programar.fk_plan_m3 = plan_planificacion_m3.id and " & _
                        "plan_planificacion_m3.estacion='" & criterio & "' and " & _
                        "CAST(plan_planificacion_m1.cantidad as int) * plan_planificacion_m2.cantidad > " & _
                        "(SELECT SUM(p2.cantidad_asignados) " & _
                        "FROM " & _
                        "public.plan_programar p2 " & _
                        "WHERE " & _
                        "plan_planificacion_m3.id=p2.fk_plan_m3) " & _
                        "group by plan_planificacion_m3.id,plan_planificacion_m1.cantidad,plan_planificacion_m2.cantidad"

            End If
            'revisar query
            If op = 6 Then
                query = "SELECT " & _
                        "plan_planificacion_m3.id, " & _
                        "plan_planificacion_m3.estacion, " & _
                        "plan_planificacion_m3.tiempo, " & _
                        "plan_planificacion_m3.observacion, " & _
                        "plan_planificacion_m3.fk_plani_m2, " & _
                        "(CAST(plan_planificacion_m1.cantidad as int) * plan_planificacion_m2.cantidad) as total " & _
                        "FROM " & _
                        "public.plan_planificacion_m3, " & _
                        "public.plan_planificacion_m2, " & _
                        "public.plan_planificacion_m1, " & _
                        "public.plan_programar " & _
                        "WHERE " & _
                        "plan_planificacion_m3.fk_plani_m2 = plan_planificacion_m2.id AND " & _
                        "plan_planificacion_m2.pk_plani_m1 = plan_planificacion_m1.id AND " & _
                        "plan_planificacion_m3.id not in (select p2.fk_plan_m3 from  public.plan_programar p2)" & _
                        "group by plan_planificacion_m3.id,plan_planificacion_m1.cantidad,plan_planificacion_m2.cantidad " & _
                        "order by id "

            End If
            'obtiene m3 planificadas a medias
            If op = 7 Then
                query = "SELECT " & _
                        "plan_planificacion_m3.id, " & _
                        "plan_planificacion_m3.estacion, " & _
                        "plan_planificacion_m3.tiempo, " & _
                        "plan_planificacion_m3.observacion, " & _
                        "plan_planificacion_m3.fk_plani_m2, " & _
                        "(CAST(plan_planificacion_m1.cantidad as int) * plan_planificacion_m2.cantidad) as total " & _
                        "FROM " & _
                        "public.plan_planificacion_m3, " & _
                        "public.plan_planificacion_m2, " & _
                        "public.plan_planificacion_m1, " & _
                        "public.plan_programar " & _
                        "WHERE " & _
                        "plan_planificacion_m3.fk_plani_m2 = plan_planificacion_m2.id AND " & _
                        "plan_planificacion_m2.pk_plani_m1 = plan_planificacion_m1.id AND " & _
                        "plan_programar.fk_plan_m3 = plan_planificacion_m3.id and " & _
                        "CAST(plan_planificacion_m1.cantidad as int) * plan_planificacion_m2.cantidad > " & _
                        "(SELECT SUM(p2.cantidad_asignados) " & _
                        "FROM " & _
                        "public.plan_programar p2 " & _
                        "WHERE " & _
                        "plan_planificacion_m3.id=p2.fk_plan_m3) " & _
                        "group by plan_planificacion_m3.id,plan_planificacion_m1.cantidad,plan_planificacion_m2.cantidad"

            End If

            Return BD.ObtenerPlanificacionM3(query)
        End Function

        Public Function ObtenerEstaciones(ByVal criterio As String, ByVal op As Integer) As ArrayList

            Dim query As String = ""

            If op = 1 Then
                query = "select * from estaciones"
            End If

            If op = 2 Then
                query = "select * from estaciones where id=" & criterio
            End If

            Return BD.ObtenerEstaciones(query)
        End Function

        Public Function ObtenerBibliotecaTecnica(ByVal criterio As String, ByVal op As Integer) As ArrayList

            Dim query As String = ""

            If op = 1 Then
                query = "select * from plan_biblioteca_tecnica order by nombre"
            End If

            If op = 2 Then
                query = "select * from plan_biblioteca_tecnica where id=" & criterio
            End If

            Return BD.ObtenerBibliotecaTecnica(query)
        End Function

        Public Function ObtenerPlanificacionProgramar(ByVal criterio As String, ByVal op As Integer)
            Dim query As String = ""

            If op = 1 Then
                query = "select * from plan_programar"
            End If

            If op = 2 Then
                query = "select * from plan_programar where id =" & criterio
            End If

            Return BD.ObtenerPlanificacionProgramar(query)
        End Function

        Public Function ObtenerPlanificacionProgramarSumaTotal(ByVal criterio As Integer)

            query = "SELECT " & _
                    "plan_programar.fk_plan_m3, " & _
                    "sum(plan_programar.cantidad_terminados)as cantidad_terminados, " & _
                    "sum(plan_programar.cantidad_asignados)as cantidad_asignados " & _
                    "FROM " & _
                    "public.plan_programar " & _
                    "WHERE " & _
                    "plan_programar.fk_plan_m3=" & criterio & _
                    "group by " & _
                    "plan_programar.fk_plan_m3"

            Return BD.ObtenerPlanificacionProgramarSumaTotal(query)
        End Function

       
#End Region

#Region "ingresar"
        Public Sub IngresarCriterioBusqueda(ByVal list As ListItem)
            Dim query As String = "insert into criterioconsultaplanificacion (nombre,dato) values('" _
                                  & list.Text + "','" _
                                  & list.Value(+"') ")
            BD.EjecutarSinRetorno(query)
        End Sub

        Public Sub ingresarPlanificacionPrincipal(ByVal plani As Planificacion)
            Dim query As String = "insert into plan_planificacion (ot_fk,fecha,k) values(" _
                                 & CStr(plani.Ot_fk) + ",'" _
                                 & plani.fecha + "', " _
                                 & plani.K + ") "

            BD.EjecutarSinRetorno(query)
        End Sub

        Public Sub ingresarPlanificacionM1(ByVal plani As Planificacion_M1)
            Dim query As String = "insert into plan_planificacion_m1 (marca,cantidad,descripcion,kguni,observacion,plano,planificado,porcentaje_avance,fk_planificacion) values('" _
                                & plani.marca + "','" _
                                & plani.Cantidad + "','" _
                                & plani.Descripcion + "'," _
                                & CStr(plani.Kguni) + ",'" _
                                & plani.Observacion + "','" _
                                & plani.Plano + "'," _
                                & CStr(plani.Planificado) + ",'" _
                                & plani.Porcentaje_avance + "'," _
                                & CStr(plani.Fk_planificacion) + ") "
            BD.EjecutarSinRetorno(query)
        End Sub

        Public Sub ingresarPlanificacionM2(ByVal plani As Planificacion_M2)
            Dim query As String = "insert into plan_planificacion_m2 (marca,descripcion,cantidad,largo,ancho,observacion,pk_plani_m1) values('" _
                                       & plani.marca + "','" _
                                       & plani.Descripcion + "'," _
                                       & CStr(plani.Cantidad) + "," _
                                       & CStr(plani.Largo) + "," _
                                       & CStr(plani.Ancho) + ",'" _
                                       & plani.Observacion + "'," _
                                       & CStr(plani.Fk_plani_m1) + ") "
            BD.EjecutarSinRetorno(query)
        End Sub

        Public Sub ingresarPlanificacionM3(ByVal plani As Planificacion_M3)
            Dim query As String = "insert into plan_planificacion_m3 (estacion,tiempo,observacion,fk_plani_m2) values('" _
                                     & CStr(plani.Estacion) + "'," _
                                     & CStr(plani.Tiempo) + ",'" _
                                     & CStr(plani.Observacion) + "'," _
                                     & CStr(plani.Fk_plani_m2) + ") "
            BD.EjecutarSinRetorno(query)
        End Sub

        Public Sub ingresarBibliotecaTecnica(ByVal bt As Biblioteca_Tecnica)
            Dim query As String = "insert into plan_biblioteca_tecnica (nombre,peso,descripcion,tipo,superficie) values('" _
                                    & (bt.Nombre) + "'," _
                                    & (bt.Peso.ToString) + ",'" _
                                    & (bt.Descripcion) + "','" _
                                    & (bt.Tipo) + "'," _
                                    & (bt.Superfficie.ToString) + ") "
            BD.EjecutarSinRetorno(query)
        End Sub

        Public Sub ingresarProgramacion(ByVal pro As Programar)
            Dim query As String = "insert into plan_programar (fk_plan_m3,f_inicio,f_fin,f_planificado,cantidad_asignados,cantidad_terminados,fk_empleado) values(" _
                         & CStr(pro.Fk_plan_m3) + ",'" _
                         & (pro.Fecha_inicio) + "','" _
                         & (pro.Fecha_fin) + "','" _
                         & (pro.Fecha_planificado) + "'," _
                         & CStr(pro.Cantidad_asignados) + "," _
                         & CStr(pro.Cantidad_terminados) + ",'" _
                         & (pro.Rutmpleado) + "') "
            BD.EjecutarSinRetorno(query)
        End Sub

#End Region

#Region "Update"

        Public Sub UpdateEstadoPm1(ByVal estado As Boolean, ByVal id As Integer)
            Dim query As String = "update plan_planificacion_m1 set planificado=" & estado & " where id= " & id
            Try
                BD.EjecutarSinRetorno(query)
            Catch ex As Exception

            End Try

        End Sub



#End Region

    End Class
End Namespace